Imports System.Data.SqlClient
Imports System.Text
Imports System.Configuration

Namespace DB
    Public Class MSSqlDB

#Region "Private Methods"
        Private oSQLDB As SqlConnection
        Private oReader As SqlDataReader
        Private daDB As New SqlDataAdapter
        Private oSQLCmd As SqlCommand
        Private dsDB As New DataSet
        Private dsDt As New DataTable
        Private m_Error As String
        Private strB As New StringBuilder
        Private dr As DataRow
        Private Parameters As New List(Of SqlParameter)
        Dim ParamColl As New List(Of SqlParameter)
        Dim _MyTran As SqlTransaction
        Dim _IsTran As Boolean

        Public Sub New()
            oSQLCmd = New SqlCommand
            oSQLCmd.CommandTimeout = 0
        End Sub

        Private Function PivotTable(ByVal source As DataTable, ByVal HeaderFix As Boolean) As DataTable
            Try
                Dim dest As New DataTable("Pivoted" + source.TableName)
                dest.Columns.Add(" ")
                Dim dummy As Integer = 1
                If HeaderFix Then
                    For Each g As DataRow In source.Rows
                        dest.Columns.Add(g(0).ToString + "(" + dummy.ToString + ")")
                        dummy = dummy + 1
                    Next
                Else
                    For Each g As DataRow In source.Rows
                        dest.Columns.Add(g(0).ToString)
                    Next
                End If
                Dim i As Integer
                For i = 0 To source.Columns.Count - 2
                    dest.Rows.Add(dest.NewRow())
                Next
                Dim r, c As Integer
                For r = 0 To dest.Rows.Count - 1
                    For c = 0 To dest.Columns.Count - 1
                        If c = 0 Then
                            dest.Rows(r)(0) = source.Columns(r + 1).ColumnName
                        Else
                            dest.Rows(r)(c) = source.Rows(c - 1)(r + 1)
                        End If
                    Next
                Next
                Return dest
                dest.Dispose()
                dest = Nothing
            Catch ex As Exception
                Throw New Exception(ex.Message, ex)
                Return Nothing
            End Try
        End Function
#End Region

#Region "Transaction Methods"
        ''' <summary>
        ''' gets and sets a SqlConnection object ,also it could be used when you want to involve more than one classes in a transaction
        ''' *this must be used when you are working in .net transacions
        ''' </summary>
        ''' <remarks></remarks>
        Public Property Connection() As SqlConnection
            Get
                Return oSQLDB
            End Get
            Set(ByVal value As SqlConnection)
                oSQLDB = value
            End Set
        End Property
        ''' <summary>
        ''' gets and sets a sqltransaction object ,also it could be used when you want to involve more than one classes in a transaction
        ''' *this must be used only when you are working in .net transacions
        ''' </summary>
        ''' <remarks></remarks>
        Public Property MyTran() As SqlTransaction
            Get
                Return _MyTran
            End Get
            Set(ByVal value As SqlTransaction)
                _MyTran = value
            End Set
        End Property
        ''' <summary>
        ''' Flag to let know to the class if there is a transaction , also it could be used when you want to involve more than one classes in a transaction
        ''' *this must be used only when you are working in .net transacions
        ''' </summary>
        ''' <remarks></remarks>
        Public Property IsTran() As Boolean
            Get
                Return _IsTran
            End Get
            Set(ByVal value As Boolean)
                _IsTran = value
            End Set
        End Property
        ''' <summary>
        ''' Starts a Database Transaction
        ''' *this must be used only when you are working in .net transacions
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub BeginTran()
            oSQLDB.Open()
            _MyTran = oSQLDB.BeginTransaction
            _IsTran = True
        End Sub
        ''' <summary>
        ''' Commits the database transaction
        ''' *this must be used only when you are working in .net transacions
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub Commit()
            _MyTran.Commit()
            CloseDB()
            _IsTran = False
        End Sub
        ''' <summary>
        ''' Rolls back a Transaction
        ''' *this must be used only when you are working in .net transacions
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub RollBack()
            _MyTran.Rollback()
            CloseDB()
            _IsTran = False
        End Sub

#End Region

#Region "Public Methods"
        ''' <summary>
        ''' Releases all the resources 
        ''' </summary>
        ''' <remarks></remarks>
        ''' 
        Public Sub Dispose()
            oSQLDB.Dispose()
            oReader = Nothing
            daDB.Dispose()
            oSQLCmd.Dispose()
            dsDB.Dispose()
            dsDt.Dispose()
            m_Error = Nothing
            strB = Nothing
            dr = Nothing
            Parameters = Nothing
            ParamColl = Nothing
            If Not IsNothing(_MyTran) Then
                _MyTran.Dispose()
            End If
            _IsTran = Nothing
        End Sub
        ''' <summary>
        ''' make one conection with one sql database 
        ''' </summary>
        ''' <param name="strServer">server where are the database</param>
        ''' <param name="strDB">name of the database</param>
        ''' <param name="strUser">name of the user</param>
        ''' <param name="strPwd">password of the user</param>
        ''' <param name="iTimeOut">time out</param>
        ''' <returns>if the conection was successfully send true, on other case send false</returns>
        ''' <remarks></remarks>
        Public Function ConnectDB(ByVal strServer As String, ByVal strDB As String, ByVal strUser As String, ByVal strPwd As String, Optional ByVal iTimeOut As Integer = 0) As Boolean
            Try
                Dim ConnStr As New SqlConnectionStringBuilder
                ConnStr.DataSource = strServer
                ConnStr.ConnectTimeout = iTimeOut
                ConnStr.InitialCatalog = strDB
                ConnStr.UserID = strUser
                ConnStr.Password = strPwd
                ConnStr.PersistSecurityInfo = True

                oSQLDB = New SqlConnection(ConnStr.ConnectionString)
                Return True
            Catch ex As Exception
                Throw New Exception(ex.Message, ex)
            End Try
        End Function
        ''' <summary>
        ''' make a connection string from one database
        ''' </summary>
        ''' <param name="ConnectionString">name for the conectionstring</param>
        ''' <returns>if the conection string was successfully made send true, in other case send false</returns>
        ''' <remarks></remarks>
        Public Function ConnectDB(ByVal ConnectionString As String) As Boolean
            Try
                Dim ConnStr As New SqlConnectionStringBuilder(ConnectionString)
                ConnStr.ConnectTimeout = 0

                oSQLDB = New SqlConnection(ConnStr.ConnectionString)
                Return True
            Catch ex As Exception
                Throw New Exception(ex.Message, ex)
            End Try
        End Function
        ''' <summary>
        ''' get the connection string named 'ConnectionInfo' from the web.config file.
        ''' </summary>
        ''' <returns>if the conection string was successfully made send true, in other case send false</returns>
        ''' <remarks></remarks>
        Public Function ConnectDB() As Boolean
            Try
                Dim ConnStr As New SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings("ConnectionInfo").ConnectionString())
                ConnStr.ConnectTimeout = 0

                oSQLDB = New SqlConnection(ConnStr.ConnectionString)
                Return True
            Catch ex As Exception
                Throw New Exception(ex.Message, ex)
            End Try
        End Function
        ''' <summary>
        ''' Close the database conection
        ''' </summary>
        ''' <returns>if the conection string was successfully close send true, in other case send false</returns>
        ''' <remarks></remarks>
        Public Function CloseDB() As Boolean
            Try
                If (oSQLDB.State = ConnectionState.Open) Then
                    oSQLDB.Close()
                    SqlConnection.ClearAllPools()
                End If
                'Dispose()
                Return True
            Catch ex As Exception
                Throw New Exception(ex.Message, ex)
            End Try
        End Function
        ''' <summary>
        ''' check if the database conection is open
        ''' </summary>
        ''' <returns>if the conection string is open send true, in other case send false</returns>
        ''' <remarks></remarks>
        Public Function DBIsOpen() As Boolean
            Return oSQLDB.State = ConnectionState.Open
        End Function

        ''' <summary>
        ''' Sql Function of read
        ''' </summary>
        ''' <param name="strSQL">Comand of sql</param>
        ''' <param name="Type">type of command</param>
        ''' <returns>sqlReader from the query</returns>
        ''' <remarks></remarks>
        Public Function ToReader(ByVal strSQL As String, ByVal Type As CommandType) As SqlDataReader
            Try
                oSQLCmd.CommandType = Type
                oSQLCmd.CommandText = strSQL
                oSQLCmd.Connection = oSQLDB

                OpenDataBase()
                SetConnectionProperties()

                oReader = oSQLCmd.ExecuteReader()
                Return oReader
            Catch ex As Exception
                Throw New Exception(ex.Message, ex)
            Finally
                SaveParameterCollection()
                'CloseDB()
            End Try
        End Function

        ''' <summary>
        ''' close the reader
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub CloseReader()
            If Not oReader Is Nothing Then
                If Not oReader.IsClosed Then
                    oReader.Close()
                End If
            End If
        End Sub

        ''' <summary>
        ''' this clear the commad parameters
        ''' </summary>
        ''' <remarks></remarks>
        Private Sub ClearParameters()
            If oSQLCmd.Parameters.Count > 0 Then
                oSQLCmd.Parameters.Clear()
            End If
        End Sub

        Private Sub OpenDataBase()
            If (_IsTran = False) Then
                If oSQLDB.State = ConnectionState.Closed Then
                    oSQLDB.Open()
                    oSQLCmd.CommandTimeout = 0
                    oSQLCmd.Connection = oSQLDB
                End If
            End If
        End Sub

        Private Sub SetConnectionProperties()
            If (_IsTran = False) Then
                oSQLCmd.Connection = oSQLDB
            Else
                oSQLCmd.Connection = oSQLDB
                oSQLCmd.Transaction = _MyTran
            End If
        End Sub

        Private Sub CheckRollBack()
            If (_IsTran = True) Then
                RollBack()
            End If
        End Sub

        Private Sub CheckCloseDB()
            If (_IsTran = False) Then
                CloseDB()
            End If
        End Sub

        ''' <summary>
        ''' make a dataset
        ''' </summary>
        ''' <param name="strSQL">query of sql</param>
        ''' <param name="Table">name from the table</param>
        ''' <param name="Type">type of command</param>
        ''' <returns>dataset from the query</returns>
        ''' <remarks></remarks>
        Public Function ToDataset(ByVal strSQL As String, ByVal Table As String, ByVal Type As CommandType) As DataSet
            Try
                dsDB.Tables.Clear()
                oSQLCmd.CommandType = Type
                oSQLCmd.CommandText = strSQL

                OpenDataBase()
                SetConnectionProperties()

                daDB.SelectCommand = oSQLCmd
                daDB.Fill(dsDB, Table)
                Return dsDB
            Catch ex As Exception
                CheckRollBack()
                Throw New Exception(ex.Message, ex)
            Finally
                ClearParameters()
                CheckCloseDB()
            End Try
        End Function
        ''' <summary>
        ''' make a dataset
        ''' </summary>
        ''' <param name="strSQL">query of sql</param>
        ''' <param name="Type">type of command</param>
        ''' <returns>dataset from the query</returns>
        ''' <remarks></remarks>
        Public Function ToDataTable(ByVal strSQL As String, ByVal Type As CommandType) As DataTable
            Try
                oSQLCmd.CommandType = Type
                oSQLCmd.CommandText = strSQL

                OpenDataBase()
                SetConnectionProperties()

                daDB.SelectCommand = oSQLCmd
                daDB.Fill(dsDt)
                Return dsDt
            Catch ex As Exception
                CheckRollBack()
                Throw New Exception(ex.Message, ex)
            Finally
                ClearParameters()
                CheckCloseDB()
            End Try
        End Function
        ''' <summary>
        ''' return a scalar value
        ''' </summary>
        ''' <param name="strSQL">query of sql</param>
        ''' <param name="Type">type of command</param>
        ''' <returns>dataset from the query</returns>
        ''' <remarks></remarks>
        Public Function ExecuteScalar(ByVal strSQL As String, ByVal Type As CommandType) As Object
            Dim Value As New Object
            Try
                oSQLCmd.CommandType = Type
                oSQLCmd.CommandText = strSQL

                OpenDataBase()
                SetConnectionProperties()

                Value = oSQLCmd.ExecuteScalar
            Catch ex As Exception
                CheckRollBack()
                Throw New Exception(ex.Message, ex)
            Finally
                SaveParameterCollection()
                CheckCloseDB()
            End Try

            Return Value
        End Function
        ''' <summary>
        ''' Execute one command in sql
        ''' </summary>
        ''' <param name="strSQL">sql command</param>
        ''' <param name="Type">type of sql command</param>
        ''' <returns>return the number of rows affected</returns>
        ''' <remarks></remarks>
        Public Function ExcCommand(ByVal strSQL As String, ByVal Type As CommandType) As Integer
            Try
                oSQLCmd.CommandType = Type
                oSQLCmd.CommandText = strSQL

                OpenDataBase()
                SetConnectionProperties()

                Return oSQLCmd.ExecuteNonQuery
            Catch ex As Exception
                CheckRollBack()
                Throw New Exception(ex.Message, ex)
            Finally
                SaveParameterCollection()
                CheckCloseDB()
            End Try
        End Function
        ''' <summary>
        ''' Send the result of one query to text with delimiter
        ''' </summary>
        ''' <param name="sQuery">Query of SQL</param>
        ''' <param name="Type">Type of command</param>
        ''' <param name="Delimeter">Char of the delimeter</param>
        ''' <param name="BreakLine">Break line for the query</param>
        ''' <returns>Text with delemiter</returns>
        ''' <remarks></remarks>
        Public Function QueryResultToDelimeterText(ByVal sQuery As String, ByVal Type As CommandType, ByVal Delimeter As Char, Optional ByVal BreakLine As String = vbCrLf) As String
            Dim ds As New DataSet
            Dim x As Integer
            Try
                strB.Remove(0, strB.Length)
                ds = ToDataset(sQuery, "Query", Type)
                If Not ds Is Nothing Then
                    For Each dr In ds.Tables("Query").Rows
                        For x = 0 To ds.Tables("Query").Columns.Count - 1
                            If x = ds.Tables("Query").Columns.Count - 1 Then
                                strB.Append(dr(x) & BreakLine)
                            Else
                                strB.Append(dr(x) & Delimeter)
                            End If
                        Next
                    Next
                End If
                If strB.ToString = "" Then strB.Append("0")
                Return strB.ToString
                strB = Nothing
            Catch ex As Exception
                Throw New Exception(ex.Message, ex)
            End Try
        End Function
        ''' <summary>
        ''' add parameters to the command
        ''' </summary>
        ''' <param name="Name">name of the command</param>
        ''' <param name="Type">tyoe of the command</param>
        ''' <param name="Value">values to add</param>
        ''' <param name="Direction">direction of the parameter</param>
        ''' <remarks></remarks>
        Public Sub AddParameter(ByVal Name As String, ByVal Type As SqlDbType, ByVal Value As String, Optional ByVal Size As Integer = 0, Optional ByVal Direction As ParameterDirection = ParameterDirection.Input)
            Try
                'Parameters.Add(New SqlParameter(Name, Type))
                'If Size > 0 Then Parameters(Parameters.Count - 1).Size = Size
                'Parameters(Parameters.Count - 1).Direction = Direction
                'Parameters(Parameters.Count - 1).Value = Value
                Dim NewParameter As New SqlParameter
                NewParameter.ParameterName = Name
                NewParameter.Direction = Direction
                NewParameter.Value = Value
                NewParameter.Size = Size
                NewParameter.DbType = Type

                oSQLCmd.Parameters.Add(NewParameter)
            Catch ex As Exception
                Throw New Exception(ex.Message, ex)
            End Try
        End Sub
        Public Sub AddParameter(ByVal pParameterName As String, ByVal pDbType As SqlDbType)
            oSQLCmd.Parameters.Add(pParameterName, pDbType)
        End Sub
        Public Sub AddParameter(ByVal pParameterName As String, ByVal pDbType As SqlDbType, ByVal pValue As Object)
            oSQLCmd.Parameters.Add(pParameterName, pDbType).Value = pValue
        End Sub
        Public Sub AddParameter(ByVal pParameterName As String, ByVal pDbType As SqlDbType, ByVal pValue As Object, ByVal pParameterDirection As ParameterDirection)
            Dim NewParameter As New SqlParameter
            NewParameter.ParameterName = pParameterName
            NewParameter.Direction = pParameterDirection
            NewParameter.Value = pValue
            NewParameter.DbType = pDbType

            oSQLCmd.Parameters.Add(NewParameter)
        End Sub
        Public Sub AddParameter(ByVal pParameterName As String, ByVal pDbType As SqlDbType, ByVal pSize As Int32, ByVal pValue As Object)
            oSQLCmd.Parameters.Add(pParameterName, pDbType, pSize).Value = pValue
        End Sub
        Public Sub AddParameter(ByVal pParameterName As String, ByVal pDbType As SqlDbType, ByVal pParameterDirection As ParameterDirection)
            Dim NewParameter As New SqlParameter
            NewParameter.ParameterName = pParameterName
            NewParameter.Direction = pParameterDirection
            NewParameter.DbType = pDbType

            oSQLCmd.Parameters.Add(NewParameter)
        End Sub
        Public Sub AddParameter(ByVal pParameterName As String, ByVal pDbType As SqlDbType, ByVal pSize As Int32, ByVal pParameterDirection As ParameterDirection)
            Dim NewParameter As New SqlParameter
            NewParameter.ParameterName = pParameterName
            NewParameter.Direction = pParameterDirection
            NewParameter.DbType = pDbType
            NewParameter.Size = pSize

            oSQLCmd.Parameters.Add(NewParameter)
        End Sub
        Public Sub AddParameter(ByVal pParameterName As String, ByVal pDbType As SqlDbType, ByVal pSize As Int32, ByVal pValue As Object, ByVal pParameterDirection As ParameterDirection)
            Dim NewParameter As New SqlParameter
            NewParameter.ParameterName = pParameterName
            NewParameter.Direction = pParameterDirection
            NewParameter.Value = pValue
            NewParameter.DbType = pDbType
            NewParameter.Size = pSize

            oSQLCmd.Parameters.Add(NewParameter)
        End Sub

        ''' <summary>
        ''' get the type parameters from one command
        ''' </summary>
        ''' <param name="Name">name of the command</param>
        ''' <returns>return the sql tyoe from the parameter</returns>
        ''' <remarks></remarks>
        ''' 
        Public Function GetParameter(ByVal Name As String) As Object
            Dim Value As New Object

            Dim i As Int32
            For i = 0 To ParamColl.Count - 1
                If (ParamColl.Item(i).ParameterName = Name) Then
                    Value = ParamColl.Item(i).Value
                End If
            Next

            Return Value
        End Function
        Private Sub SaveParameterCollection()
            ParamColl.Clear()
            For Each Param As SqlParameter In oSQLCmd.Parameters
                ParamColl.Add(Param)
            Next

            ClearParameters()
        End Sub

        ''' <summary>
        ''' This function returns a DataTable with the result as a Pivot Table with columns as rows and rows as columns. 
        ''' </summary>
        ''' <param name="Query">The SQL Statment that will bring data from the database.</param>
        ''' <param name="HeaderFix">Variable that defines if the columns had the same name.</param>
        ''' <returns>DataTable with the result as a Pivot Table</returns>
        ''' <remarks>Note that the first column of your data will be taken as the row of the column titles of the Pivot table</remarks>
        Public Function PivotTableFromQuery(ByVal Query As String, ByVal HeaderFix As Boolean, ByVal ConnectionStringName As String) As DataTable
            Dim tbl As DataTable = New DataTable("DummyTable")
            Try
                Dim da As New SqlDataAdapter(Query, oSQLDB)
                da.Fill(tbl)
                da.Dispose()
                da = Nothing
            Catch ex As Exception
                Throw New Exception(ex.Message, ex)
            End Try
            Return PivotTable(tbl, HeaderFix)
            tbl.Dispose()
            tbl = Nothing
        End Function
        ''' <summary>
        ''' This function returns a DataTable with the result as a Pivot Table with columns as rows and rows as columns. 
        ''' </summary>
        ''' <param name="scDataSet">The Data Set with your data.</param>
        ''' <param name="DataSetTableName">The name of the table inside the Data Set</param>
        ''' <param name="HeaderFix">Variable that defines if the columns had the same name.</param>
        ''' <returns>DataTable with the result as a Pivot Table</returns>
        ''' <remarks>Note that the first column of your data will be taken as the row of the column titles of the Pivot table</remarks>
        Public Function PivotTableFromDataSet(ByVal scDataSet As DataSet, ByVal DataSetTableName As String, ByVal HeaderFix As Boolean, ByVal ConnectionStringName As String) As DataTable
            Dim tbl As DataTable = scDataSet.Tables(DataSetTableName)
            Try
                Return PivotTable(tbl, HeaderFix)
            Catch ex As Exception
                Throw New Exception(ex.Message, ex)
            End Try
        End Function
#End Region
    End Class
End Namespace